const db = require('../config/db');
const ApiError = require('../utils/ApiError');

class ExperimentModel {
  // 获取所有实验
  static async getAll() {
    try {
      console.log("get all experiments");
      const [experiments] = await db.query(`
        SELECT e.*, 
          (SELECT COUNT(*) FROM experiment_classes ec WHERE ec.experiment_id = e.id) AS classCount
        FROM experiments e
      `);

      // 获取每个实验的目标
      for (const exp of experiments) {
        exp.objectives = await this.getObjectives(exp.id);
        exp.resources = await this.getResources(exp.id);
      }

      console.log(experiments);
      return experiments;
    } catch (err) {
      throw new ApiError(`获取实验列表失败: ${err.message}`, 500);
    }
  }

  // 获取单个实验详情
  static async getById(id) {
    try {
      const [experiments] = await db.query('SELECT * FROM experiments WHERE id = ?', [id]);

      if (experiments.length === 0) {
        return null;
      }

      const experiment = experiments[0];
      experiment.objectives = await this.getObjectives(id);
      experiment.resources = await this.getResources(id);
      experiment.classes = await this.getClasses(id);

      return experiment;
    } catch (err) {
      throw new ApiError(`获取实验详情失败: ${err.message}`, 500);
    }
  }

  // 获取实验目标
  static async getObjectives(experimentId) {
    const [objectives] = await db.query(`
      SELECT * FROM experiment_objectives 
      WHERE experiment_id = ?
    `, [experimentId]);
    return objectives;
  }

  // 获取实验资源
  static async getResources(experimentId) {
    const [resources] = await db.query(`
      SELECT * FROM experiment_resources 
      WHERE experiment_id = ?
    `, [experimentId]);
    return resources;
  }

  // 获取关联班级
  static async getClasses(experimentId) {
    const [classes] = await db.query(`
      SELECT c.id, c.name, ec.deadline 
      FROM experiment_classes ec
      JOIN classes c ON ec.class_id = c.id
      WHERE ec.experiment_id = ?
    `, [experimentId]);
    return classes;
  }

  // 创建实验
  static async create({ title, short_description, full_description, course_category, duration, image_url, objectives, resources }) {
    try {
      // 开始事务
      await db.query('START TRANSACTION');

      // 插入实验基本信息
      const [result] = await db.query(`
        INSERT INTO experiments 
          (title, short_description, full_description, course_category, duration, image_url)
        VALUES (?, ?, ?, ?, ?, ?)
      `, [title, short_description, full_description, course_category, duration, image_url]);

      const experimentId = result.insertId;

      // 插入实验目标
      for (const objective of objectives) {
        await db.query(`
          INSERT INTO experiment_objectives 
            (experiment_id, objective)
          VALUES (?, ?)
        `, [experimentId, objective.objective]);
      }

      // 插入实验资源
      for (const resource of resources) {
        await db.query(`
          INSERT INTO experiment_resources 
            (experiment_id, name, type, file_path, is_public)
          VALUES (?, ?, ?, ?, ?)
        `, [experimentId, resource.name, resource.type, resource.file_path, resource.is_public]);
      }

      // 提交事务
      await db.query('COMMIT');

      return await this.getById(experimentId);
    } catch (err) {
      // 回滚事务
      await db.query('ROLLBACK');
      throw new ApiError(`创建实验失败: ${err.message}`, 500);
    }
  }

  // 更新实验
  static async update(id, { title, short_description, full_description, course_category, duration, image_url, objectives, resources }) {
    try {
      // 检查实验是否存在
      const [experiments] = await db.query('SELECT * FROM experiments WHERE id = ?', [id]);
      if (experiments.length === 0) {
        return null;
      }

      // 开始事务
      await db.query('START TRANSACTION');

      // 更新实验基本信息
      await db.query(`
        UPDATE experiments 
        SET title = ?, short_description = ?, full_description = ?, 
            course_category = ?, duration = ?, image_url = ?
        WHERE id = ?
      `, [title, short_description, full_description, course_category, duration, image_url, id]);

      // 删除旧的目标
      await db.query(`
        DELETE FROM experiment_objectives 
        WHERE experiment_id = ?
      `, [id]);

      // 插入新的目标
      for (const objective of objectives) {
        await db.query(`
          INSERT INTO experiment_objectives 
            (experiment_id, objective)
          VALUES (?, ?)
        `, [id, objective.objective]);
      }

      // 删除旧的资源
      await db.query(`
        DELETE FROM experiment_resources 
        WHERE experiment_id = ?
      `, [id]);

      // 插入新的资源
      for (const resource of resources) {
        await db.query(`
          INSERT INTO experiment_resources 
            (experiment_id, name, type, file_path, is_public)
          VALUES (?, ?, ?, ?, ?)
        `, [id, resource.name, resource.type, resource.file_path, resource.is_public]);
      }

      // 提交事务
      await db.query('COMMIT');

      return await this.getById(id);
    } catch (err) {
      // 回滚事务
      await db.query('ROLLBACK');
      throw new ApiError(`更新实验失败: ${err.message}`, 500);
    }
  }

  // 删除实验
  static async delete(id) {
    try {
      // 检查实验是否存在
      const [experiments] = await db.query('SELECT * FROM experiments WHERE id = ?', [id]);
      if (experiments.length === 0) {
        return false;
      }

      // 检查是否有关联的班级
      const [classes] = await db.query(`
        SELECT COUNT(*) AS count 
        FROM experiment_classes 
        WHERE experiment_id = ?
      `, [id]);

      if (classes[0].count > 0) {
        throw new ApiError('该实验已被班级关联，无法删除', 400);
      }

      // 开始事务
      await db.query('START TRANSACTION');

      // 删除目标
      await db.query(`
        DELETE FROM experiment_objectives 
        WHERE experiment_id = ?
      `, [id]);

      // 删除资源
      await db.query(`
        DELETE FROM experiment_resources 
        WHERE experiment_id = ?
      `, [id]);

      // 删除实验
      await db.query(`
        DELETE FROM experiments 
        WHERE id = ?
      `, [id]);

      // 提交事务
      await db.query('COMMIT');

      return true;
    } catch (err) {
      // 回滚事务
      await db.query('ROLLBACK');
      throw new ApiError(`删除实验失败: ${err.message}`, 500);
    }
  }

}

module.exports = ExperimentModel;